import connection from '../config/mysqlConnect.js';
import formatTime from './formatTime.js';
import { deepClone } from './common.js';

const handleSqlData = (e) => {
  if (e == null || e == undefined) {
    return `${null}`
  }
	if (e instanceof Array && e.length > 0) {
		return `'${e.join()}'`
	}
	if (e.constructor === Object) {
		return '[Object Object]'
	}
  switch (typeof e) {
    case 'number':
      return e
    case 'string':
      return `'${e}'`
    default:
      return `''`
  }
}

const deleteKeyWord = (data) => {
  const cloneData = deepClone(data)
  const keyWords = ['create_time','update_time','timestap']
  keyWords.forEach(w => {
    cloneData && cloneData[w] && delete cloneData[w]
  })
  return cloneData
}

/**
 * sql处理函数
 * @param {object} body 处理的数据对象,不含 pageSize pageIndex
 * @param {*} operateChar sql条件操作符 {'=': [fields,fields],'like':[fields],'in':[fields]}
 * @param {*} linkChar sql条件连接符 and | or
 * @returns 
 */
const handleSqlObj = (body, operateChar, linkChar = "and") => {
  
  
  let sets = [],operate='=',pattern='',fields=[]
  operateChar = operateChar || {'=': [],'like':[],'in':[]}
  body = deleteKeyWord(body)
  for (const key in body) {
    if (Object.hasOwnProperty.call(body, key) && body[key] !== undefined &&  body[key] !== "" && body[key] !== null && key !== 'pageSize' && key !== 'pageIndex' && key !== 'orderBy' && key !== 'sort') {

      for (let operateStr in operateChar) {
        let keys = operateChar[operateStr]
				fields=fields.concat(keys)
        if (keys.includes(key) && operateStr == 'like') {
          operate = operateStr
          pattern = `%${body[key]}%`
        } else if (keys.includes(key) && operateStr == 'in') {
          operate = operateStr
					if ((typeof body[key] == 'string' && body[key]) || (body[key] instanceof Array && body[key].length)) {
						pattern =`(${body[key] instanceof Array ? body[key].join(','): body[key]})`
					}
        } else if (keys.includes(key) && operateStr == '=') {
					operate = '='
					pattern = body[key]
				}
				else if (fields.indexOf(key) < 0){
					 operate = '='
					 pattern = body[key]
        }
      }


      switch (typeof body[key]) {
        case 'number':
          sets.push(` ${key} ${operate} ${pattern} `)
          continue
        case 'boolean':
          sets.push(` ${key} ${operate} ${pattern} `)
          continue
        case 'string':
					if (!body[key]) {
						break;
					} 
          sets.push(` ${key} ${operate} ${operate == 'in'?pattern:`'${pattern}'`} `)
          continue
        default:
					pattern && sets.push(` ${key} ${operate} ${pattern} `)
          continue
      }
    }
    if (body[key] === null || body[key] == 'null') {
      sets.push(` ${key} ${operate} NULL `)
    }
		// if (body[key] === '') {
		// 	sets.push(` ${key} = null  `)
		// }
  }
  
  
  
  return sets.join(linkChar)
}
/* 
 * @param {string} table  表名
 * @param {number} num 随机的数量
 * @param {string} filed 依据字段
 * @returns 
 */
const randSql = (table, num, filed = "id") => {
  return `SELECT * FROM ${table} as t1 WHERE t1.${filed}>=(RAND()*(SELECT MAX(${filed}) FROM ${table}))LIMIT ${num};`
}

/**
 * 
 * @param {*} table 
 * @param {*} body {key: value} | [{key:value},{key:value}]
 * @returns 
 */
const addSql = (table, body) => {


  try {
    let keys = [], values = [], mulValues = []
    if (!Array.isArray(body)) {

      for (const key in body) {
        if (Object.hasOwnProperty.call(body, key)) {
          if (body[key] != null && body[key] != undefined) {
            values.push(body[key]);
            keys.push([key])
          }
        }
      }
      keys.push(...['create_time', 'update_time'])
      values = values.map(e => {
        return handleSqlData(e)
      })
      values.push([`'${formatTime(Date.now())}'`, `'${formatTime(Date.now())}'`])
      return `insert into ${table}  (${keys.join(',')}) values (${values.join(',')})`
    } else if (body instanceof Array) {
      for (const key in body[0]) {
        keys.push(key)
      }
      keys.push(...['create_time', 'update_time'])
      body.forEach((single, index) => {
        const v = Object.values(single).map(e => handleSqlData(e)).concat(`'${formatTime(Date.now())}'`, `'${formatTime(Date.now())}'`)
        mulValues.push(v)
      })
      let str = ''

      mulValues.forEach((value, index) => {
        str += `(${value.join()})${mulValues.length > index + 1 ? ',' : ''}`
      })

      return `insert into ${table}  (${keys.join(',')}) values ${str}`
    } else {
      return ''
    }

  } catch (error) {
    return error
  }
}

/**
 * 
 * @param {string} table 
 * @param {object} body 修改的键值对 {key: value}
 * @param {object} condition 条件语句对象
 * @param {string} operate 修改的键值对的操作符 = | like
 * @param {string} linkOperate 修改的键值对的操作符 and | or
 * @returns 
 */
const putSql = (table, body, condition, operate) => {
  
  
  try {
    let handleBody = handleSqlObj(body,null,',')
    const conditionSql = handleSqlObj(condition, operate)
    for (const key in body) {
			if (Object.hasOwnProperty.call(body, key)) {
				const v = body[key];
				if (v == null || v == '' || v == undefined) {
					handleBody += `, ${key} = null`
				}
			}
		}
    return `update ${table} set ${handleBody} ` +`${conditionSql ? `where ${conditionSql}` : ''}`

  } catch (error) {
    return error
  }
}

const descSql = async (tables) => {
  const rows = {}
  for (let i = 0; i < tables.length; i++) {
    const sql = `desc ${tables[i]}`
    const row = await getResult(sql)
    rows[tables[i]] = row.map(e => `${tables[i]}.${e.Field}`)
    // if (i == 0) {
    // } else {
    //   rows[tables[i]] = row.map(e => `${tables[i].split('.')[1]}.${e.Field}`)
    // }
  }
  return rows
}

/**
 * 
 * @param {*} table 表名 
 * @param {*} body {orderBy: String , like, sort: String like and,}
 * @param {*} options {operate:{like: [fields] ， like: [fields] }}
 * @returns 
 */
const listSql = (table, body, options) => {
	
  try {
    let pageSize = body?.pageSize || 20
    let pageIndex = body?.pageIndex || 1
    let orderBy = body?.orderBy || 'id'
    let sort = body?.sort || 'desc'
    let operate = options && options.operate || '='
    let linkChar = options && options.linkChar || 'and'
    let hasPage = (options && options.hasPage) ?? true
		
    const sqlHandler = handleSqlObj(body, operate, linkChar)
    
    
    return `select * from ${table} ${sqlHandler ? `where ${sqlHandler}` : ''} order by ${orderBy} ${sort} ${hasPage? 'limit ' +(pageIndex - 1) * pageSize+','+pageSize: '' }`
  } catch (error) {
    
    return error
  }
}

/**
 * 
 * @param {*} table 表名 
 * @param {*} body {orderBy: String , like, sort: String like and,}
 * @param {*} options {operate: {like:[fields],=:[fields]} ,linkChar: and|or,hasPage: true | false}
 * @returns 
 */
export const patternSql = (table, body, options) => {
  try {
    let pageSize = body?.pageSize || 20
    let pageIndex = body?.pageIndex || 1
    let orderBy = body?.orderBy || 'id'
    let sort = body?.sort || 'desc'
    let operate = options && options.operate
    let linkChar = options && options.linkChar || 'and'
    let hasPage = options && options.hasPage || true
    const sqlHandler = handleSqlObj(body, operate, linkChar)
    return `select * from ${table} ${sqlHandler ? `where ${sqlHandler}` : ''} order by ${orderBy} ${sort} ${hasPage? 'limit ' +(pageIndex - 1) * pageSize+','+pageSize: '' }`
  } catch (error) {
    return error
  }
}



/**
 * 
 * @param {*} table 表名 
 * @param {*} body {orderBy: String like =, like, sort: String like and,}
 * @returns 
 */
const countSql = (table, body, options ) => {
  let operate = options && options.operate || '='
  let linkChar = options && options.linkChar || 'and'
    const sqlHandler = handleSqlObj(body, operate, linkChar)
    return `select count(*) as count from ${table} ${sqlHandler ? `where ${sqlHandler}` : ''}`
}




/**
 * 模糊查询
 * @param {String} table 
 * @param {object} body  模糊查询的键值对
 * @param {string} operate = | like 条件操作符 
 * @returns 
 */
export const likeSql = (table, body, operate = "like") => {
  try {
    const { pageSize = 1000, pageIndex = 1, orderBy = 'id', sort = 'desc' } = body
    let sqlHandler = handleSqlObj(body, operate)
    return `select * from ${table} ${sqlHandler ? `where ${sqlHandler}` : ''} order by ${orderBy} ${sort} limit ${pageSize} offset ${(pageIndex - 1) * pageSize}`
  } catch (error) {
    return error
  }
}


/**
 * 查询单条数据
 * @param {*} table 
 * @param {object} body 查询的键值对{key: value}
 * @param {string} operate 匹配操作符 = | like
 * @param {string} link 连接的操作符 and | or
 * @returns 
 */
const detailSql = (table, body, operate, link = 'or') => {

  const handleSql = handleSqlObj(body, operate, link)
  

  try {
    return `select * from ${table}` + (handleSql ? ` where ${handleSql}` : '')
  } catch (error) {
    return error
  }
}

/**
 * 
 * @param {string} table 表名 
 * @param {object} body 删除的键值对, {key : value}
 * @param {string} operate 删除多个键值对操作符, or | and
 * @returns 
 */
const deleteSql = (table, body, operate = 'or') => {
  let str = ''
  const keys = Object.keys(body) || []
  keys.forEach((key, index) => {
    str += `${key} = '${body[key]}' ${index < keys.length - 1 ? ` ${operate} ` : ''}`
  })
  try {
    return `delete from ${table}` + (str ? ` where ${str}` : '')
  } catch (error) {
    return error
  }
}

/**
 * 
 * @param {*} table 
 * @param {*} body 
 * @param {*} options {operate: 'or | and', keyField: '', valueField: ''}
 * @returns 
 */
export const multiDeleteSql = (table, body, options) => {
  let str = ''
  let {ids,logic = true} = body
  ids = `(${ids.join()})`
  try {
    return !logic ? `delete from ${table}` + ` where id in ${ids}` : `update ${table} set is_delete = 1 where id in ${ids}`
  } catch (error) {
    return error
  }
}

/**
 * 
 * @param {string} table 表名 
 * @param {object} body 修改的键值对 
 * @returns 
 */
export const onDuplicByFiled = (table, body) => {
  try {
    let keys = [], values = [], sets = []
    for (const key in body) {
      if (Object.hasOwnProperty.call(body, key)) {
        keys.push(key)
        values.push(body[key])
        switch (typeof body[key]) {
          case 'number':
            sets.push(`${key} = ${body[key]}`)
            continue
          case 'string':
            sets.push(`${key} = '${body[key]}'`)
            continue
          case 'boolean':
            sets.push(`${key} = ${body[key]}`)
            continue
          default:
            sets.push(`${key} = '${body[key]}'`)
            continue
        }
      }
    }

    // 处理引号问题
    values = values.map(e => {
      return handleSqlData(e)
    })
    return `INSERT INTO ${table} (${keys.join(',')}) VALUES (${values.join(',')}) ON DUPLICATE KEY UPDATE ${sets.join(',')} ; `
  } catch (error) {
    return error
  }
}




const getResult = (sql) => {
  return new Promise((resolve, reject) => {
    if (!sql || typeof sql !== 'string') {
      reject(`SQL is Error${sql}`)
    }
    connection.getConnection(function (connetErr, connect) {
      if (connetErr) {
        reject(connetErr)
        return
      }
      connect.query(sql, function (err, rows, fields) {
        if (err) reject(err);
        resolve(rows)
      })
      connect.release()
    })
  });
}

export {
  addSql, countSql, deleteSql,
  detailSql,
  getResult, listSql, putSql, randSql
};

